/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package org.scuecsz.utils;

import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 *
 * @author Administrator
 */
public class DBHandler {
    private static void debug(Object dataString) {
        System.out.println("DBHandler:" + dataString);
    }

    public static String formatData(String dataString, int type) {
        String tempString = dataString;
        switch (type) {
            case 91:
                tempString = StringUtil.formatDate(dataString);
                break;
            case 93:
                tempString = StringUtil.formatDateTime(dataString);
                break;
            default:
                tempString = StringUtil.getString(dataString);
                break;
        }
        return tempString;
    }

    public static int getMaxPageNum(String sql, List values, int size, Connection conn) throws SQLException {
        int dataSize = getDataSize(sql, values, conn);
        return getMaxPageNum(dataSize, size);
    }

    public static int getMaxPageNum(int dataSize, int size) {
        if (size == 0) {
            return 0;
        }
        return dataSize / size + (0 == dataSize % size ? 0 : 1);
    }

    public static int getDataSize(String sql, List values, Connection conn) throws SQLException {
        int size = 0;
        sql = "select count(*) from ( ".concat(sql).concat(" ) temp");
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            ps = preparedStatementInit(sql, values, conn, ps);
            rs = ps.executeQuery();
            if (rs.next()) {
                size = rs.getInt(1);
            }
        } catch (SQLException ex) {
            throw ex;
        } finally {
            ConnectionBuilder.closeResultSet(rs);
            ConnectionBuilder.closePreparedStatement(ps);
            return size;
        }
    }

    public static String getLIMITSQL(int pageNum, int size) {
        if (0 == size || pageNum <= 0) {
            return StringUtil.NULLSTRING;
        }
        int fromIndex = (pageNum - 1) * size;
        return StringUtil.LIMIT + fromIndex + StringUtil.COMMA + size;
    }

    public static PreparedStatement preparedStatementInit(String sql, List values, Connection conn, PreparedStatement ps) throws SQLException {
       debug("SQL:" + sql);
       debug("PA:" + values);
        ps = conn.prepareStatement(sql);
        if (null != values) {
            for (int i = 0; i < values.size(); i++) {
                ps.setString(i + 1, (String) values.get(i));
            }
        }
        return ps;
    }

    public static int insert(Object to, Connection conn) throws SQLException, Exception {
        int count = 0;
        if (null != to) {
            List values = new ArrayList();
            String insertSql = "insert into ".concat(JavaBeanUtil.getValue(to, "getTable")).concat("(");
            String insertColumns = StringUtil.NULLSTRING;
            String insertvalues = StringUtil.NULLSTRING;
            Field[] fields = to.getClass().getDeclaredFields();
            for (int i = 0; i < fields.length; i++) {
                String property = fields[i].getName();
                String value = JavaBeanUtil.getValue(to, StringUtil.GET.concat(property));
                if ("CREATE_TIME".equals(property)) {
                    insertColumns = insertColumns.concat(property.concat(StringUtil.COMMA));
                    insertvalues = insertvalues.concat("NOW(),");
                } else {
                    if (null != value && !StringUtil.NULLSTRING.equals(value) && !"ID".equals(property)) {
                        insertColumns = insertColumns.concat(property.concat(StringUtil.COMMA));
                        insertvalues = insertvalues.concat("?,");
                        if(property.endsWith("_DATE")||property.endsWith("_TIME")){
                            value=StringUtil.formatMySqlDate(value);
                        }
                        values.add(value);
                    }
                }
            }

            if (!StringUtil.NULLSTRING.equals(insertColumns) && !StringUtil.NULLSTRING.equals(insertvalues)) {
                insertSql = insertSql.concat(insertColumns.substring(0, insertColumns.length() - 1)).
                        concat(")values(").concat(insertvalues.substring(0, insertvalues.length() - 1)).concat(")");
                PreparedStatement ps = null;
                ResultSet rs = null;
                try {
                    ps = preparedStatementInit(insertSql, values, conn, ps);
                    count = ps.executeUpdate();
                } catch (SQLException ex) {
                    throw ex;
                } finally {
                    ConnectionBuilder.closeResultSet(rs);
                    ConnectionBuilder.closePreparedStatement(ps);
                }
            }
        } else {
            throw new Exception("TO is NULL");
        }
        return count;
    }

    public static int update(Object to, Connection conn) throws SQLException, Exception {
        int count = 0;
        if (null != to) {
            List values = new ArrayList();
            String updateSql = "update ".concat(JavaBeanUtil.getValue(to, "getTable")).concat(" set ");
            String where = " Where ID=";
            String updateColumns = StringUtil.NULLSTRING;
            Field[] fields = to.getClass().getDeclaredFields();
            for (int i = 0; i < fields.length; i++) {
                String property = fields[i].getName();
                String value = JavaBeanUtil.getValue(to, StringUtil.GET.concat(property));
                if ("ID".equals(property)) {
                    where = where.concat(value);
                } else {
                    if ("MODIFY_TIME".equals(property)) {
                        updateColumns = updateColumns.concat("MODIFY_TIME=NOW(),");
                    } else if(null != value) {
                        updateColumns = updateColumns.concat(property.concat(StringUtil.EQUAL).concat("?,"));
                        if(property.endsWith("_DATE")||property.endsWith("_TIME")){
                            value=StringUtil.formatMySqlDate(value);
                        }
                        values.add(value);
                    }
                }
            }

            if (!StringUtil.NULLSTRING.equals(updateColumns)) {
                updateSql = updateSql.concat(updateColumns.substring(0, updateColumns.length() - 1));
                PreparedStatement ps = null;
                ResultSet rs = null;
                try {
                    ps = preparedStatementInit(updateSql.concat(where), values, conn, ps);
                    count = ps.executeUpdate();
                } catch (SQLException ex) {
                    throw ex;
                } finally {
                    ConnectionBuilder.closeResultSet(rs);
                    ConnectionBuilder.closePreparedStatement(ps);
                }
            }
        } else {
            throw new Exception("TO is NULL");
        }
        return count;
    }


    public static int executeUpdate(String updateSql, List values,Connection conn) throws SQLException, Exception {
        int count = 0;
                PreparedStatement ps = null;
                try {
                     ps = preparedStatementInit(updateSql, values, conn, ps);
                    count = ps.executeUpdate();
                } catch (SQLException ex) {
                    throw ex;
                } finally {
                    ConnectionBuilder.closePreparedStatement(ps);
                }
        return count;
    }

    public static int delete(Object to, Connection conn) throws SQLException, Exception {
        int count = 0;
        if (null != to) {
            String id = JavaBeanUtil.getValue(to, "getID");
            if (!StringUtil.NULLSTRING.equals(id)) {
                String deleteSql = "delete from ".concat(JavaBeanUtil.getValue(to, "getTable")).concat("where ID=?");
                PreparedStatement ps = null;
                ResultSet rs = null;
                try {
                    ps = conn.prepareStatement(deleteSql);
                    ps.setString(1, (String) id);
                    count = ps.executeUpdate();
                } catch (SQLException ex) {
                    throw ex;
                } finally {
                    ConnectionBuilder.closeResultSet(rs);
                    ConnectionBuilder.closePreparedStatement(ps);
                }
            } else {
                throw new Exception("TD is NULL");
            }
        } else {
            throw new Exception("TO is NULL");
        }
        return count;
    }
//query

    public static ResultSet executeQuery(String sql,List values, Connection conn, PreparedStatement ps, ResultSet rs) throws SQLException {
        try {
            ps = preparedStatementInit(sql, values, conn, ps);
            rs = ps.executeQuery();
        } catch (SQLException ex) {
            throw ex;
        }
        return rs;
    }

    public static Object getObjectFromResultSet(String className, ResultSet rs) throws Exception {
        Class objClass = Class.forName(className);
        ResultSetMetaData md = rs.getMetaData();
        int columnCount = md.getColumnCount();
        Object obj = null;
        if (rs.next()) {
            obj = objClass.newInstance();
            for (int i = 1; i <= columnCount; i++) {
                JavaBeanUtil.setValue(obj, StringUtil.SET.concat(md.getColumnName(i)), formatData(rs.getString(i), md.getColumnType(i)));
            }
        }
        return obj;
    }

    public static List getObjsFromResultSet(String className, ResultSet rs, List objs) throws Exception {
        if (null == objs) {
            objs = new ArrayList();
        } else {
            objs.clear();
        }
        Class objClass = Class.forName(className);
        ResultSetMetaData md = rs.getMetaData();
        String[] columnNames;
        String[] columnTypes;
        int columnCount = md.getColumnCount();
        Object obj = null;
        if (rs.next()) {
            obj = objClass.newInstance();
            columnNames = new String[columnCount];
            columnTypes = new String[columnCount];
            for (int i = 1; i <= columnCount; i++) {
                int j = i - 1;
                columnNames[j] = md.getColumnName(i);
                columnTypes[j] = md.getColumnTypeName(i);
                JavaBeanUtil.setValue(obj, StringUtil.SET.concat(md.getColumnName(i)), formatData(rs.getString(i), md.getColumnType(i)));
            }
            objs.add(obj);
        }

        while (rs.next()) {
            obj = objClass.newInstance();
            for (int i = 1; i <= columnCount; i++) {
                JavaBeanUtil.setValue(obj, StringUtil.SET.concat(md.getColumnName(i)), formatData(rs.getString(i), md.getColumnType(i)));
            }
            objs.add(obj);
        }
        return objs;
    }

    public static List query(String className, String sql, List values, List objs, int pageNum, int size, Connection conn) throws SQLException, Exception {
        sql = sql.concat(getLIMITSQL(pageNum, size));
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            rs = executeQuery(sql, values, conn, ps, rs);
            objs = getObjsFromResultSet(className, rs, objs);
        } catch (SQLException ex) {
            throw ex;
        } finally {
            ConnectionBuilder.closePreparedStatement(ps);
            ConnectionBuilder.closeResultSet(rs);
        }
        return objs;
    }

    public static Object queryOne(String className, String sql, List values, Connection conn) throws SQLException, Exception {
        PreparedStatement ps = null;
        ResultSet rs = null;
        Object obj=null;
        try {
            rs = executeQuery(sql, values, conn, ps, rs);
            obj = getObjectFromResultSet(className, rs);
        } catch (SQLException ex) {
            throw ex;
        } finally {
            ConnectionBuilder.closePreparedStatement(ps);
            ConnectionBuilder.closeResultSet(rs);
        }
        return obj;
    }

    public static String selectString(String sql,List values,Connection conn) throws SQLException, Exception {
        String str=null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            rs = executeQuery(sql, values, conn, ps, rs);
            if(rs.next()){
                str=rs.getString(1);
            }
        } catch (SQLException ex) {
            throw ex;
        } finally {
            ConnectionBuilder.closePreparedStatement(ps);
            ConnectionBuilder.closeResultSet(rs);
        }
        return str;
    }
}

